/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package server;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author User
 */
public class DB {

    private Connection connection;
    private Statement stat;
    private ResultSet result;

    public DB(){
        String db_url = "jdbc:mysql://localhost:3306/dobogdb";
        String db_user = "root";
        String db_pass = "";
        try {
            Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            connection = DriverManager.getConnection (db_url, db_user, db_pass);
        }catch (SQLException e) {System.out.println(e);	}
        catch (InstantiationException e) {System.out.println(e);}
        catch (IllegalAccessException e) {System.out.println(e);}
        catch (ClassNotFoundException e) {System.out.println(e);}
    }

    public void executeQuery(String q) throws SQLException{
         stat = connection.createStatement();
         result = stat.executeQuery(q);
    }

    public void executeUpdate(String q) throws SQLException{
         stat = connection.createStatement();
         stat.executeUpdate(q);
    }

    public void execute(String q) throws SQLException{
         stat = connection.createStatement();
         if (stat.execute(q)){
             result = stat.getResultSet();
         }
    }

    public boolean ceklogin(String username, String password) throws SQLException
    {
        this.executeQuery("SELECT * FROM user WHERE username LIKE '"+ username +"' AND password LIKE '"+ password +"'");
        int count = this.result.getRow();
        result.beforeFirst();
        if(result.next()){
            return true;
        }
        return false;
    }

    public boolean isUserExist(String username) throws SQLException{
        this.executeQuery("SELECT * FROM user WHERE username LIKE '" + username + "'");
        int count = this.result.getRow();
        return (count > 0);
    }

    public void addUser(String username, String password) throws SQLException{
        this.executeUpdate("INSERT INTO user (username,password) VALUES ('" + username + "','" + password + "')");
    }

    public boolean isFileExist(String filename) throws SQLException{
        this.executeQuery("SELECT * FROM filelist WHERE filename LIKE '" + filename + "'");
        int count = this.result.getRow();
        return (count > 0);
    }

    public void addFile(String filename, String path, long datemodified, String user) throws SQLException{
        this.executeUpdate("INSERT INTO filelist(filename,path,datemodified,user) VALUES ('" + filename + "','" + path + "'," + datemodified+ ",'" + user + "')");
    }

    public void deleteFile(String filename) throws SQLException{
        this.executeUpdate("DROP TABLE filelist WHERE filename = '" + filename + "'");
    }

    public void editFile(String filename, long datemodified) throws SQLException{
        this.executeUpdate("UPDATE filelist SET datemodified = " + datemodified + " WHERE filename = '" + filename + "'");
    }

    public long getDateModified(String filename) throws SQLException{
        this.executeQuery("SELECT * from filelist WHERE filename = '" + filename + "'");
        return result.getLong("datemodified");
    }

    public String getPath(String filename) throws SQLException{
        this.executeQuery("SELECT * from filelist WHERE filename = '" + filename + "'");
        return result.getString("path");
    }

    public String getUser(String filename) throws SQLException{
        this.executeQuery("SELECT * from filelist WHERE filename = '" + filename + "'");
        return result.getString("user");
    }

    public ArrayList<InfoFile> getFileUser(String username) throws SQLException{
        System.out.println("getFileUser");
        ArrayList<InfoFile> listFile = new ArrayList<InfoFile>();
        this.executeQuery("SELECT * from filelist WHERE user = '" + username + "'");
        result.beforeFirst();
        while(result.next()){
            InfoFile data = new InfoFile(result.getString("filename"), result.getString("path"), result.getLong("datemodified"));
            listFile.add(data);
            System.out.println(data.convertToMessageInfoFile());
        }
        return listFile;
    }
}
